Relational database servers such as, for example, SQL™ Server from MICROSOFT Corp. of Redmond, Wash., perform a variety of complex calculations to retrieve and manipulate data. Such complex calculations are often not possible using traditional structured query language (SQL) or other relational operators such as GROUP BY, WHERE, etc. A table-valued-function (TVF) can solve this problem by providing a mechanism to introduce special logic into a query operation tree. The TVF class of functions allows scalar arguments as input and produces a series of rows as output. Special logic can be inserted in TVF functions to perform special transformations not supported by traditional relational operators.
Efficient evaluation of TVF's is difficult. These functions often do not have predictable performance behavior since they can contain arbitrary procedural code. This code can include complex logic that calls an external component, possibly on a remote machine. Since query optimizers often use cost estimation models to pick efficient execution strategies, TVF's may pose difficulties with respect to optimization as their performance is hard to predict. Furthermore, since TVF's are used to arbitrarily extend database systems, it is difficult to build a single cost estimation that covers all possible forms of TVF extensibility. Additionally, users who implement TVF's often have limited knowledge of the internals of a query processor. Such limited knowledge makes it difficult to supply accurate and trustable information about the relative execution cost of a function compared to existing internal operations.
Another difficulty with respect to evaluation of TVF's is that some TVF implementations require the result of the TVF be pre-materialized in, for example, a temporary table, before the execution of the remainder of the query. This pre-materialization requires a slow spooling of query results and also limits the flexibility of the TVF by requiring that inputs to the TVF be known before any other portion of the query can be executed. This restriction is not desirable because it is often advantageous to supply a portion of the query such as, for example, a column in a table, as a parameter to the TVF invocation.
As a result of the difficulties set forth above, users of TVF's in conventional relational database systems have had to accept sub-par performance and limited capability as the price of TVF extensibility. Thus, there is a need in the art for systems and methods for efficiently evaluating a query that invokes a TVF. The present invention satisfies these and other needs.